# Query Builder
The Query Builder provides a visual designer to design how data should be retrieved and transformed. Additionally, Query Builder provides a testing interface to help validate created queries and input parameters. Query Builder is accessible from App Designer widgets, Form Designer fields, and various Workflow Designer actions.# Accessing Query Builder
Query Builder is made available throughout the PMG application based on context and the area of the application being used. Below are areas Query Builder can be accessed.
# App Designer Widgets, Data Sources
Query Builder is available from widgets with data source properties.
# Form Designer, Form Field Data Sources
Query Builder is accessible depending on the selected data source for fields.
# Using Query Builder
The Query Builder interface provides for the following configuration options.
Data Source: provides a select list of available data sources for which to build a query. Available data source types to use with Query Builder are SQL data sources, Records, Data Workflows, and JavaScript. The PMG core databases are also available for custom SQL queries.
Table: For SQL sources, once a source is selected, available tables from the selected source are provided in the select list. When a table is selected, the columns from the table will be displayed for use.
Max result size: Provides an optional limit on the number of records to be returned from a query.
Once a data source and table are selected, the columns for the data source are presented in a list where various rules or options can be set for the data.
# Query Builder with SQL sources
An example query and test are shown below.
# Selecting and ordering return data
After a table is selected, columns to be returned in the query are set by selecting the respective "Return" checkbox for each.
Columns may be reordered by drag/drop using the control for each. --> 
# Conditions for returning data
Conditions may be set as needed to limit data returned. Conditions are added as groups of "All of these are true" (a SQL 'and') or "Any of these are true" (a SQL 'or'). Fields are added to the condition with the "Add Field" button. Nesting of conditions is supported by the "Add Sub Group" button.
Condition values may also be dynamic based on available data. In the below example, records whose value for City match the value in the select list named "City" will be returned.
# Sorting Data
Sorting on one or more columns can be done using the Sorting section at the bottom of the page. Select any sort fields and choose "Ascending" or "Descending".
# Testing Queries
Queries can be tested in the Testing section provided. The Testing section also shows the underlying query which is generated from the defined query. If there are any dynamic values, like the widget value "" shown below, a section for Test Values is given where test data can be provided. To validate the query, click the "Run Test" button to see returned results.
# Custom Queries
Queries may be edited by selecting "Custom Query" and editing the query as needed. This custom query may then be saved. Custom queries are "one way" and will not be converted back into visual queries. To remove the custom query and return to a visual query, deselect the "Custom Query" option.
# Query Builder with JavaScript
To use JavaScript as a source of data, insert the code into the provided Testing input, along with any dynamic values coming from the browser session, URL parameters, form fields, or widget values. Dynamic values are then provided as a set of "Test Values" where you can enter test data into the respective Value column for each and then use "Run Test" to view a query result from the query and test data provided. The Testing input window provides for type-ahead selection of available dynamic inputs.
# Query Builder with Records
To use Records with Query Builder, select an available Record Collection. For the selected collection, available columns are displayed which can be selected to be returned in the query.
# Query Builder and Record Collection Virtual Columns
Virtual columns are transformations of the underlying data as a new column. Transformations support concatenation, substrings, capitalization, as well as any processing from the C# language as script.
Individual columns may be overridden to be returned as a transformation by selecting the "Toggle Per Column Expressions" button. Additional virtual columns may be added by clicking "Add Virtual Column" for each new column needed.
Fields can be concatenated as below to generate a new column.
The above will result in a new column per below
# Query Builder Scripted Transformations
C# as script is available to transform columns using C# syntax. The syntax to apply C# as script to transform a value is as follows:
{(ColumnName).EXPRESSION}
The above indicates the value of the ColumnName for this row should be returned and the Expression will be ran against it.
For example, the below will return a string adding 10 to the value of a number column, "Year"
Expires {(Year)+10}
Virtual Columns can refer to other virtual columns. Below is an example which results in the following.
